﻿CREATE TABLE [dbo].[Opportunity] (
    [OpportunityId]         UNIQUEIDENTIFIER NOT NULL,
    [PriorityCode]          INT              NULL,
    [OpportunityRatingCode] INT              NULL,
    [Name]                  NVARCHAR (300)   NULL,
    [Description]           NVARCHAR (MAX)   NULL,
    [EstimatedValue]        MONEY            NULL,
    [EstimatedCloseDate]    DATETIME         NULL,
    [CloseProbability]      INT              NULL,
    [ActualValue]           MONEY            NULL,
    [ActualCloseDate]       DATETIME         NULL,
    [OriginatingLeadId]     UNIQUEIDENTIFIER NULL,
    [CreatedOn]             DATETIME         NULL,
    [CreatedById]           UNIQUEIDENTIFIER NULL,
    [ModifiedOn]            DATETIME         NULL,
    [ModifiedById]          UNIQUEIDENTIFIER NULL,
    [VersionNumber]         ROWVERSION       NULL,
    [StatusCode]            INT              NULL,
    [TransactionCurrencyId] UNIQUEIDENTIFIER NULL,
    [CustomerId]            UNIQUEIDENTIFIER NULL,
    [OwnerId]               UNIQUEIDENTIFIER NULL,
    [StepId]                UNIQUEIDENTIFIER NULL,
    [StepName]              NVARCHAR (200)   NULL,
    [SalesStageCode]        INT              NULL,
    [LeadSourceCode]        INT              NULL,
    [OpportunityTypeCode]   INT              NULL,
    [DepartmentId]          UNIQUEIDENTIFIER NULL,
    [IsDeleted]             BIT              CONSTRAINT [DF_Opportunity_IsDeleted] DEFAULT ((0)) NOT NULL,
    [ProjectTypeCode]       INT              NULL,
    [TechnologyCode]        INT              NULL,
    [TechnicianId]          UNIQUEIDENTIFIER NULL,
    [LatestFeedbackOn]      DATETIME         NULL,
    [ExpectedStartOn]       DATETIME         NULL,
    [EstimatedWorkAmount]   FLOAT (53)       NULL,
    [ClosedTime]            DATETIME         NULL,
    CONSTRAINT [PK_Opportunity] PRIMARY KEY CLUSTERED ([OpportunityId] ASC),
    CONSTRAINT [FK_Opportunity_Department] FOREIGN KEY ([DepartmentId]) REFERENCES [dbo].[Department] ([DepartmentId]),
    CONSTRAINT [FK_Opportunity_Lead] FOREIGN KEY ([OriginatingLeadId]) REFERENCES [dbo].[Lead] ([LeadId]),
    CONSTRAINT [FK_Opportunity_TransactionCurrency] FOREIGN KEY ([TransactionCurrencyId]) REFERENCES [dbo].[TransactionCurrency] ([TransactionCurrencyId]),
    CONSTRAINT [FK_Opportunity_User] FOREIGN KEY ([OwnerId]) REFERENCES [dbo].[User] ([UserId]),
    CONSTRAINT [FK_Opportunity_User_CreatedBy] FOREIGN KEY ([CreatedById]) REFERENCES [dbo].[User] ([UserId]),
    CONSTRAINT [FK_Opportunity_User_ModifiedBy] FOREIGN KEY ([ModifiedById]) REFERENCES [dbo].[User] ([UserId]),
    CONSTRAINT [FK_Opportunity_User_TechnicianId] FOREIGN KEY ([TechnicianId]) REFERENCES [dbo].[User] ([UserId])
);

